﻿using DBHelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL.QDD
{
   public class ItemBrandServices
    {
        /// <summary>
        /// 查询识别码对应的所有的编码
        /// </summary>
        /// <param name="id">识别码</param>
        /// <param name="itype">0_外购件, 1_自制件</param>
        /// <returns></returns>
        public DataTable GetPLTtem(string id,int itype)
        {
            string sql = "select Item,sum(Qty) as Qty from PL_Item where Con_PickingID=@id and Itype=@Itype group by Item";
            SqlParameter[] para = new SqlParameter[]
            {
                new SqlParameter("@id",id),
                new SqlParameter("@Itype",itype)
            };
            try
            {
                SqlDataReader reader = SqlHelper.GetReader(sql, para);
                if (!reader.HasRows)
                {
                    return null;
                }
                else
                {
                    DataTable dt = new DataTable();
                    dt.Load(reader);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //查询编码对应的所有的编码信息
        public DataTable GetItemBrand(string item)
        {
            string sql = "select Item,Brand,Model,Origin,1 as Qty from Item_Brand where Item=@item";
            SqlParameter[] para = new SqlParameter[]
            {
                new SqlParameter("@item",item)
            };
            try
            {
                SqlDataReader reader = SqlHelper.GetReader(sql, para);
                if (!reader.HasRows)
                {
                    return null;
                }
                else
                {
                    DataTable dt = new DataTable();
                    dt.Load(reader);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //判断合同号是自制件还是外购件
        public int GetCon_PickingID(string Con_PickingID,out string countryid,out string con)
        {
            countryid = null;
            con = null;
            string sql = "SELECT A.ContractNO,B.CountryID,B.ZZJSign FROM Con_Picking AS A LEFT JOIN Country AS B ON A.CountryCode=B.CountryID where Con_PickingID=@Con_PickingID";
            SqlParameter[] para = new SqlParameter[]
            {
                new SqlParameter("@Con_PickingID",Con_PickingID)
            };
            try
            {
                SqlDataReader reader = SqlHelper.GetReader(sql, para);
                if (!reader.HasRows)
                {
                    return -1;//国家资料表中没有此国家代码,请维护进去
                }
                else
                {
                    int x = 0;
                    while (reader.Read())
                    {
                        countryid = reader["CountryID"].ToString();
                        con = reader["ContractNO"].ToString();
                        x = Convert.ToInt32(reader["ZZJSign"].ToString());
                    }
                    return x;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        //判断签单数据表中有没有提交的识别码数据
        public bool BoolSignData(string id)
        {
            string sql = "SELECT COUNT(SignDataID) AS SID FROM SignData where Con_PickingID=@id";
            SqlParameter[] para = new SqlParameter[]
            {
                new SqlParameter("@id",id)
            };
            try
            {
                object ob= SqlHelper.GetOneResult(sql, para);
                if (ob.ToString()=="0")
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        //判断合同号还有多少份单没有签单
        public int PositionNum(string con)
        {
            string sql = $"SELECT COUNT(Con_PickingID) AS num FROM Con_Picking WHERE ContractNO='{con}' AND PackageQtyStatus !=6";
            try
            {
                return Convert.ToInt32(SqlHelper.GetOneResult(sql));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //获取签单表中有多种品牌和型号的编码数据
        public DataTable GetSignDataItem(string con)
        {
            string sql = $"select ContractNO,Item,Brand,Model,Origin,sum(PCS) as sumPCS,count(distinct(Item+Brand+Model+Origin)) as counts from SignData WHERE ContractNO='{con}' group by ContractNO,Item,Brand,Model,Origin ";
            sql += "having Item in (select Item from ( ";
            sql += $"select Item,count(distinct(Item+Brand+Model+Origin)) as qty from SignData WHERE ContractNO='{con}' group by Item having count(distinct(Item+Brand+Model+Origin))>1) as a)";
            try
            {
                SqlDataReader reader = SqlHelper.GetReader(sql);
                if (!reader.HasRows)
                {
                    return null;
                }
                DataTable dt = new DataTable();
                dt.Load(reader);
                return dt;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public List<string> GetQdcon()
        {
            string sql = "SELECT DISTINCT(ContractNO) AS ContractNO FROM Con_Picking WHERE PackageQtyStatus=5";
            try
            {
                SqlDataReader reader = SqlHelper.GetReader(sql);
                if (!reader.HasRows)
                {
                    return null;
                }
                List<string> list = new List<string>();
                while (reader.Read())
                {
                    list.Add(reader["ContractNO"].ToString());
                }
                return list;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public List<string> GetQdid(string con)
        {
            string sql = $"SELECT Con_PickingID FROM Con_Picking WHERE ContractNO='{con}' AND PackageQtyStatus=5";
            try
            {
                SqlDataReader reader = SqlHelper.GetReader(sql);
                if (!reader.HasRows)
                {
                    return null;
                }
                List<string> list = new List<string>();
                while (reader.Read())
                {
                    list.Add(reader["Con_PickingID"].ToString());
                }
                return list;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        //判断合同号是否需要拍照
        public int ConIsPhoto(string con)
        {
            string sql = $"select COUNT(Item) AS sumitem from ConItem where ContractNO='{con}' and ISPhoto='Y'";
            try
            {
              object ob=  SqlHelper.GetOneResult(sql);
                if (Convert.ToInt32(ob)>0)
                {
                    return 5;
                }
                else
                {
                    return 6;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
    }
}
